/*数据库增、删、改、查api的封装*/
/**
 * 数据库增、删、改、查封装
 */
//mysql连接池配置文件
const mysql = require("mysql");
const oracledb = require('oracledb');
const des = require("./des.js");


let connection = (req, callback) =>{
    const pool = mysql.createPool(req.body);
    pool.getConnection((err, connection) => {
        if(err){
            console.log("建立连接失败", err);
            callback(err)
        }else {
            connection.query(`show tables`, async (err, result) => {
                if(err){
                    console.log("建立连接失败", err);
                    callback(err)
                }else{
                    let token = des.encryptDes(JSON.stringify(req.body),des.key);
                    callback({code:200,token:token})
                    connection.release();
                }
            })
        }
    });
}
/**
 * tableColumns
 */
let queryTables = (req, callback) => {
    let db = req.body.db;
    let sql = `SELECT d.TABLE_ROWS,d.TABLE_NAME,d.TABLE_COMMENT FROM information_schema.TABLES d WHERE d.TABLE_SCHEMA='${db}'`;
    getPool(req.headers.token).getConnection((err, connection) => {
        connection.query(`${sql}`, async (err, result) => {
            let data = []
            for(let i=0;i<result.length;i++){
                let dataSource = result[i];
                let tableColumnSql = `SELECT d.ORDINAL_POSITION AS 'seq',d.COLUMN_NAME AS 'name',d.COLUMN_COMMENT AS 'desp',d.DATA_TYPE AS 'type', d.CHARACTER_MAXIMUM_LENGTH AS 'length',d.IS_NULLABLE AS 'nullable',d.COLUMN_DEFAULT AS 'default' from information_schema.COLUMNS d WHERE d.TABLE_NAME = '${result[i].TABLE_NAME}' AND d.TABLE_SCHEMA = '${db}'`
                const columns = await query(`${tableColumnSql}`);
                dataSource.columns = columns;
                data.push(dataSource);
            }
            callback(data);
            connection.release();
        });
    });
};


/**
 * databases
 */
let databases = (req, callback) => {
    let sql = `SHOW DATABASES`;
    getPool(req.headers.token).getConnection((err, connection) => {
        connection.query(`${sql}`, (err, result) => {
            callback(result);
            connection.release();
        });
    });
};


/**
 * sql
 */
let sql = (req, callback) => {
    let sql = req.body.sql;
    console.log(sql)
    getPool(req.headers.token).getConnection((err, connection) => {
        connection.query(`${sql}`, (err, result) => {
            if(err){
                console.log("建立连接失败", err);
                callback(err)
            }else{
                callback({code:200,result:result})
            }
            connection.release();
        });
    });
};


// 接收一个sql语句 以及所需的values
// 这里接收第二参数values的原因是可以使用mysql的占位符 '?'
// 比如 query(`select * from my_database where id = ?`, [1])

let query = function( sql, values ) {
    // 返回一个 Promise
    return new Promise(( resolve, reject ) => {
        pool.getConnection(function(err, connection) {
            if (err) {
                reject( err )
            } else {
                connection.query(sql, values, ( err, rows) => {

                    if ( err ) {
                        reject( err )
                    } else {
                        resolve( rows )
                    }
                    // 结束会话
                    connection.release()
                })
            }
        })
    })
}

const getPool =  (token) => {
      let dbConfig = des.decryptDes(token, des.key);
      pool = mysql.createPool(JSON.parse(dbConfig));
      return pool;
};

module.exports = {
    queryTables,
    databases,
    sql,
    connection
}
